﻿/******************************************************************************
'Description:装试问题上报看板类
'Copyright (c) : 通力凯顿（北京）系统集成有限公司
'Writer:Zhunan
'create Date:2019.7.2
 ********************************************************************************/
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
namespace ReportBusiness
{
    public class ZSProblemLookBoardBusiness
    {
        private uMESReportOracleDAL dal = new uMESReportOracleDAL();

        public DataTable GetAllFactory()
        {
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT f.factoryname,ua.attributevalue,f.factoryid FROM factory f");
            sb.AppendLine("LEFT JOIN Userattribute ua ON ua.parentid = f.factoryid AND ua.userattributename = '分厂代码'");
            sb.AppendLine("WHERE ua.attributevalue IS NOT NULL AND ua.attributevalue IN ('601','609','600','607','603')");
            return dal.GetDataTable(sb.ToString());
        }

        //查询--装试分厂本月问题回复比 modify zhunan 2019.12.4
        public DataTable GetZSThisMonthProblemRate(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("type");//类型
            dtResult.Columns.Add("name");
            dtResult.Columns.Add("total");
            dtResult.Columns.Add("finished");
            dtResult.Columns.Add("notFinished");

            for (int i = 0; i < 4; i++)
            {
                DataRow dr = dtResult.NewRow();
                dr["total"] = 0;
                dr["finished"] = 0;
                dr["notFinished"] = 0;
                dtResult.Rows.Add(dr);
            }

            dtResult.Rows[0]["type"] = "wtful";
            dtResult.Rows[0]["name"] = "问题回复率";

            dtResult.Rows[1]["type"] = "ythfl";
            dtResult.Rows[1]["name"] = "一天回复率";

            dtResult.Rows[2]["type"] = "wtcql";
            dtResult.Rows[2]["name"] = "问题超期率";

            dtResult.Rows[3]["type"] = "wtgbl";
            dtResult.Rows[3]["name"] = "问题关闭率";

            #region 问题回复率
            //查询问题回复比--分子（回复时间不为空且回复时间在本月）
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,COUNT(1) huifuFZ");
            sb.AppendLine("FROM ufcproblem up ");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtHuiFuFZ = dal.GetDataTable(sb.ToString());

            //查询问题回复比--分母（回复时间不为空且回复时间在本月 + 回复时间为空）
            sb = new StringBuilder();
            sb.AppendLine("SELECT t.factoryid,COUNT(1) huifuFM");
            sb.AppendLine("FROM ( ");
            sb.AppendLine("     SELECT up.factoryid");
            sb.AppendLine("     FROM ufcproblem up ");
            sb.AppendLine("     WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("     UNION ALL");
            sb.AppendLine("     SELECT up.factoryid");
            sb.AppendLine("     FROM ufcproblem up");
            sb.AppendLine("     WHERE up.ufcshenhedate IS NULL ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine(") t  GROUP BY t.factoryid");

            DataTable dtHuiFuFM = dal.GetDataTable(sb.ToString());
            #endregion

            #region 一天回复率
            //查询一天回复率--分子（回复时间不为空 && 回复时间在本月 && 回复时间-提出时间小于1天）
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,COUNT(1) ythfFZ");
            sb.AppendLine("FROM ufcproblem up ");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1 ");
            sb.AppendLine("      AND TRUNC(up.ufcshenhedate-up.ufccreatedate) < 1");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtYthfFZ = dal.GetDataTable(sb.ToString());

            //查询一天回复率--分母（回复时间不为空  && 回复时间在本月）
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,COUNT(1) ythfFM");
            sb.AppendLine("FROM ufcproblem up ");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtYthfFM = dal.GetDataTable(sb.ToString());
            #endregion

            #region 问题超期率
            //查询--问题超期率--分子(未关闭且当前已超期)
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,COUNT(up.description) cqlFZ");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcstatus <> 0 AND up.ufcguanbidate IS NULL AND TRUNC(SYSDATE - up.ufcchulidate) >= 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtCqlFZ = dal.GetDataTable(sb.ToString());


            //查询--问题超期率--分母(未关闭问题)
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,COUNT(up.description) cqlFM");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcstatus <> 0 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtCqlFM = dal.GetDataTable(sb.ToString());

            #endregion

            #region 问题关闭率
            //查询--问题关闭率--分子（关闭时间不为空 && 关闭时间在本月）
            sb = new StringBuilder();
            sb.AppendLine("SELECT up.factoryid,Count(up.description) gbFZ");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL");
            sb.AppendLine("      AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("GROUP BY up.factoryid");

            DataTable dtGbFZ = dal.GetDataTable(sb.ToString());


            //查询--问题关闭率--分母（1＋2）
            //1: 本月关闭的问题
            //2: 未关闭的问题
            sb = new StringBuilder();
            sb.AppendLine("SELECT t.factoryid,COUNT(t.description) gbFM");
            sb.AppendLine("FROM (");
            sb.AppendLine("     SELECT up.factoryid,up.description");
            sb.AppendLine("     FROM ufcproblem up");
            sb.AppendLine("     WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL");
            sb.AppendLine("           AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine("UNION ALL ");
            sb.AppendLine("     SELECT up.factoryid,up.description");
            sb.AppendLine("     FROM ufcproblem up");
            sb.AppendLine("     WHERE up.ufcstatus <> 0 ");
            if (para.ContainsKey("factoryid"))
            {
                sb.AppendFormat(" AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            sb.AppendLine(") t GROUP BY t.factoryid");

            DataTable dtGbFM = dal.GetDataTable(sb.ToString());
            #endregion

            //问题回复率
            dtResult.Rows[0]["total"] = dtHuiFuFM.Rows.Count > 0 ? dtHuiFuFM.Rows[0]["huifuFM"] : 0;
            dtResult.Rows[0]["finished"] = dtHuiFuFZ.Rows.Count > 0 ? dtHuiFuFZ.Rows[0]["huifuFZ"] : 0;
            dtResult.Rows[0]["notFinished"] = Convert.ToDecimal(dtResult.Rows[0]["total"]) - Convert.ToDecimal(dtResult.Rows[0]["finished"]);

            //一天回复率
            dtResult.Rows[1]["total"] = dtYthfFM.Rows.Count > 0 ? dtYthfFM.Rows[0]["ythfFM"] : 0;
            dtResult.Rows[1]["finished"] = dtYthfFZ.Rows.Count > 0 ? dtYthfFZ.Rows[0]["ythfFZ"] : 0;
            dtResult.Rows[1]["notFinished"] = Convert.ToDecimal(dtResult.Rows[1]["total"]) - Convert.ToDecimal(dtResult.Rows[1]["finished"]);
            if (dtResult.Rows[1]["total"].ToString() == "0")
            {
                dtResult.Rows[1]["total"] = 1;
                dtResult.Rows[1]["finished"] = 0;
                dtResult.Rows[1]["notFinished"] = 1;
            }

            //问题超期率
            dtResult.Rows[2]["total"] = dtCqlFM.Rows.Count > 0 ? dtCqlFM.Rows[0]["cqlFM"] : 0;
            dtResult.Rows[2]["finished"] = dtCqlFZ.Rows.Count > 0 ? dtCqlFZ.Rows[0]["cqlFZ"] : 0;
            dtResult.Rows[2]["notFinished"] = Convert.ToDecimal(dtResult.Rows[2]["total"]) - Convert.ToDecimal(dtResult.Rows[2]["finished"]);

            //问题关闭率
            dtResult.Rows[3]["total"] = dtGbFM.Rows.Count > 0 ? dtGbFM.Rows[0]["gbFM"] : 0;
            dtResult.Rows[3]["finished"] = dtGbFZ.Rows.Count > 0 ? dtGbFZ.Rows[0]["gbFZ"] : 0;
            dtResult.Rows[3]["notFinished"] = Convert.ToDecimal(dtResult.Rows[3]["total"]) - Convert.ToDecimal(dtResult.Rows[3]["finished"]);

            return dtResult;
        }
        
        //查询--装试分厂问题上报总数 modify zhunan 2019.12.4
        public DataTable GetZSProblemTotalCountData(Dictionary<string, object> para)
        {
            StringBuilder sb = new StringBuilder();
            //查询问题总数（未关闭问题+本月已关闭问题）
            sb.AppendLine("SELECT COUNT(1) total");
            sb.AppendLine("FROM (");
            sb.AppendLine("     SELECT up.description FROM ufcproblem up");
            sb.AppendFormat("     WHERE up.ufcstatus<>0 AND up.factoryid = '{0}'", para["factoryid"]);
            sb.AppendLine("     UNION ALL");
            sb.AppendLine("     SELECT up.description FROM ufcproblem up");
            sb.AppendFormat("     WHERE up.factoryid = '{0}' AND up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ",para["factoryid"]);
            sb.AppendLine(")");

            DataTable dtTotal = dal.GetDataTable(sb.ToString());

            //查询回复总数（本月回复问题总数）
            sb = new StringBuilder();
            sb.AppendLine("SELECT COUNT(1) huifu");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1");
            sb.AppendFormat("    AND up.factoryid = '{0}'", para["factoryid"]);

            DataTable dtHuiFu = dal.GetDataTable(sb.ToString());

            //查询关闭总数（本月关闭问题总数）
            sb = new StringBuilder();
            sb.AppendLine("SELECT COUNT(1) guanbi");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1");
            sb.AppendFormat("    AND up.factoryid = '{0}'", para["factoryid"]);

            DataTable dtGuanBi = dal.GetDataTable(sb.ToString());

            //查询超期总数（问题未关闭且当前已超期）
            sb = new StringBuilder();
            sb.AppendLine("SELECT COUNT(1) chaoqi");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("WHERE up.ufcguanbidate IS NULL AND up.ufcstatus <> 0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1");
            sb.AppendFormat("  AND up.factoryid = '{0}'", para["factoryid"]);

            DataTable dtChaoQi = dal.GetDataTable(sb.ToString());

            DataTable dtResult = new DataTable();
            dtResult.Columns.Add("total");
            dtResult.Columns.Add("huifu");
            dtResult.Columns.Add("guanbi");
            dtResult.Columns.Add("chaoqi");

            DataRow dr = dtResult.NewRow();

            dr["total"] = dtTotal.Rows[0]["total"];
            dr["huifu"] = dtHuiFu.Rows[0]["huifu"];
            dr["guanbi"] = dtGuanBi.Rows[0]["guanbi"];
            dr["chaoqi"] = dtChaoQi.Rows[0]["chaoqi"];

            dtResult.Rows.Add(dr);

            return dtResult;
        }

        //查询--装试分厂责任单位回复情况 modify zhunan 2019.12.4
        public DataTable GetRespWorkcenterProblemData(Dictionary<string, object> para)
        {
            string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE w.factoryid='{0}'", para["factoryid"]);

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("total");//问题总数
            dtResult.Columns.Add("huifu");//回复数
            dtResult.Columns.Add("chaoqi");//超期数

            //查询问题总数
            //问题未关闭或本月关闭
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT m.workcentername,COUNT(1) total");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("     SELECT rw.workcentername FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendFormat("   WHERE up.ufcguanbidate IS NULL AND up.ufcstatus <> 0 AND up.factoryid='{0}'", para["factoryid"]);//未关闭

            sb.AppendLine("     UNION ALL");

            sb.AppendLine("     SELECT rw.workcentername FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendFormat("   WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("           AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            sb.AppendLine(") m GROUP BY m.workcentername");

            DataTable dtTotal = dal.GetDataTable(sb.ToString());


            //查询回复数
            //本月已回复数
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) huifu");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1");
            sb.AppendFormat("    AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtHuiFu = dal.GetDataTable(sb.ToString());

            //查询超期数
            //问题未关闭且当前已超期
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) chaoqi");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = up.workcenterid");
            sb.AppendLine("WHERE up.ufcstatus <>  0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1");
            sb.AppendFormat(" AND up.factoryid='{0}' ", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtChaoQi = dal.GetDataTable(sb.ToString());

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("workcentername = '{0}'", dr["workcentername"].ToString());

                dr["total"] = dtTotal.Select(filter).Length > 0 ? dtTotal.Select(filter)[0]["total"] : 0;

                dr["huifu"] = dtHuiFu.Select(filter).Length > 0 ? dtHuiFu.Select(filter)[0]["huifu"] : 0;

                dr["chaoqi"] = dtChaoQi.Select(filter).Length > 0 ? dtChaoQi.Select(filter)[0]["chaoqi"] : 0;

            }

            return dtResult;
        }

        //查询--装试分厂提出单位回复情况 modify zhunan 2019.12.4
        public DataTable GetZSCreateWorkcenterProblemData(Dictionary<string, object> para)
        {
            string sql = string.Format("SELECT w.workcentername FROM workcenter w WHERE w.factoryid='{0}'", para["factoryid"]);

            DataTable dtResult = dal.GetDataTable(sql);

            dtResult.Columns.Add("total");//问题总数
            dtResult.Columns.Add("huifu");//回复数
            dtResult.Columns.Add("wancheng");//完成数
            dtResult.Columns.Add("guanbi");//关闭数
            dtResult.Columns.Add("chaoqi");//超期数

            //查询问题总数
            //问题未关闭或本月关闭
            StringBuilder sb = new StringBuilder();
            sb.AppendLine("SELECT m.workcentername,COUNT(1) total");
            sb.AppendLine("FROM");
            sb.AppendLine("(");
            sb.AppendLine("     SELECT rw.workcentername ");
            sb.AppendLine("     FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = sv.workcenterid");
            sb.AppendFormat("   WHERE up.ufcguanbidate IS NULL AND up.ufcstatus <> 0 AND up.factoryid='{0}'", para["factoryid"]);//未关闭

            sb.AppendLine("     UNION ALL");

            sb.AppendLine("     SELECT rw.workcentername ");
            sb.AppendLine("     FROM ufcproblem up");
            sb.AppendLine("     LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid");
            sb.AppendLine("     LEFT JOIN workcenter rw ON rw.workcenterid = sv.workcenterid");
            sb.AppendFormat("   WHERE up.ufcstatus = 0 AND up.ufcguanbidate IS NOT NULL AND up.factoryid='{0}'", para["factoryid"]);//本月关闭
            sb.AppendLine("           AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1 ");
            sb.AppendLine(") m GROUP BY m.workcentername");

            DataTable dtTotal = dal.GetDataTable(sb.ToString());

            //查询回复数
            //本月已回复数
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) huifu");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = sv.workcenterid");
            sb.AppendLine("WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1");
            sb.AppendFormat("    AND up.factoryid='{0}'", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtHuiFu = dal.GetDataTable(sb.ToString());

            //查询超期数
            sb = new StringBuilder();
            sb.AppendLine("SELECT rw.workcentername,COUNT(1) chaoqi");
            sb.AppendLine("FROM ufcproblem up");
            sb.AppendLine("LEFT JOIN sessionvalues sv ON sv.employeeid = up.ufccreateemployeeid");
            sb.AppendLine("LEFT JOIN workcenter rw ON rw.workcenterid = sv.workcenterid");
            sb.AppendLine("WHERE up.ufcstatus <>  0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1");//未关闭且当前已超期
            sb.AppendFormat(" AND up.factoryid='{0}' ", para["factoryid"]);
            sb.AppendLine("GROUP BY rw.workcentername");

            DataTable dtChaoQi = dal.GetDataTable(sb.ToString());

            foreach (DataRow dr in dtResult.Rows)
            {
                string filter = string.Format("workcentername = '{0}'",dr["workcentername"].ToString());

                dr["total"] = dtTotal.Select(filter).Length > 0 ? dtTotal.Select(filter)[0]["total"] : 0;
                dr["huifu"] = dtHuiFu.Select(filter).Length > 0 ? dtHuiFu.Select(filter)[0]["huifu"] : 0;
                dr["chaoqi"] = dtChaoQi.Select(filter).Length > 0 ? dtChaoQi.Select(filter)[0]["chaoqi"] : 0;
            }

            return dtResult;
        }

        //查询--问题上报总数明细
        public DataTable GetProblemDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((SYSDATE-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcstatus<>0                          
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            sql += @"
UNION ALL 
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((up.ufcguanbidate-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') 
        AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1
";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            foreach (DataRow dr in retR.DBTable.Rows)
            {
                if (Convert.ToDecimal(dr["delayday"]) < 0)
                {
                    dr["delayday"] = 0;
                }
            }

            return retR.DBTable;
        }

        //查询--超期总数明细 modify zhunan 2019.12.4
        public DataTable GetTotalChaoQiDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((SYSDATE-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcguanbidate IS NULL  AND up.ufcstatus <> 0 AND TRUNC(SYSDATE - up.ufcchulidate) >= 1
";

            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            foreach (DataRow dr in retR.DBTable.Rows)
            {
                if (Convert.ToDecimal(dr["delayday"]) < 0)
                {
                    dr["delayday"] = 0;
                }
            }

            return retR.DBTable;
        }

        //查询--回复总数明细
        public DataTable GetHuiFuDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((NVL(up.ufcguanbidate,sysdate)-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcshenhedate IS NOT NULL AND up.ufcshenhedate >= trunc(sysdate,'month') AND up.ufcshenhedate <= trunc(last_day(sysdate)) + 1 
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            foreach (DataRow dr in retR.DBTable.Rows)
            {
                if (Convert.ToDecimal(dr["delayday"]) < 0)
                {
                    dr["delayday"] = 0;
                }
            }

            return retR.DBTable;
        }

        //查询--关闭总数明细
        public DataTable GetGuanBiDetails(Dictionary<string, object> para, out int pageCount, out int rowCount)
        {
            string sql = @"
SELECT up.description,w.workcentername,e.fullname,up.ufcchulidate,ce.fullname createperson,up.ufccreatedate,up.ufcresult,
        DECODE(up.ufcwtdj,'3','一般（3天）',2,'急（1天）',1,'紧急（4H）',0,'特急（1H）') priority,
        CASE WHEN up.ufcstatus=0 THEN '已完成' WHEN up.ufcstatus=1 THEN '未处理' WHEN up.ufcstatus=2 THEN '处理中' WHEN up.ufcstatus=3 THEN '处理完' END AS ufcstatus,
        ROUND((up.ufcguanbidate-up.ufcchulidate),2) AS delayday
FROM ufcproblem up
LEFT JOIN Workcenter w ON w.workcenterid = up.workcenterid
LEFT JOIN employee e ON e.employeeid = up.ufctoemployeeid
LEFT JOIN employee ce ON ce.employeeid = up.ufccreateemployeeid
LEFT JOIN sessionvalues sv ON sv.employeeid = ce.employeeid
LEFT JOIN workcenter cw ON cw.workcenterid = sv.workcenterid
WHERE up.ufcguanbidate IS NOT NULL AND up.ufcguanbidate >= trunc(sysdate,'month') AND up.ufcguanbidate <= trunc(last_day(sysdate)) + 1  
";
            if (para.ContainsKey("factoryid"))
            {
                sql += string.Format("  AND up.factoryid = '{0}' ", para["factoryid"]);
            }
            if (para.ContainsKey("createWorkcenter"))
            {
                if (para["createWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND cw.workcentername LIKE '%{0}%' ", para["createWorkcenter"].ToString());
                }
            }
            if (para.ContainsKey("resWorkcenter"))
            {
                if (para["resWorkcenter"].ToString() != "All")
                {
                    sql += string.Format("  AND w.workcentername LIKE '%{0}%' ", para["resWorkcenter"].ToString());
                }
            }

            uMESPagingDataDTO retR = dal.GetPagingDataIns(sql, int.Parse(para["page"].ToString()), int.Parse(para["rows"].ToString()));
            pageCount = Convert.ToInt32(retR.PageCount);
            rowCount = Convert.ToInt32(retR.RowCount);

            foreach (DataRow dr in retR.DBTable.Rows)
            {
                if (Convert.ToDecimal(dr["delayday"]) < 0)
                {
                    dr["delayday"] = 0;
                }
            }

            return retR.DBTable;
        }
    }
}
